﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Comm;
using LogNet;
namespace DBHelper
{
    public class SqlHelper
    {
        private string sqlcon;

        public SqlHelper(string sqlconstr)
        {
            sqlcon = sqlconstr;
        }
        public DataTable SqlExecuteReader(string sqlstr)
        {
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(sqlstr))
            {
                using (SqlConnection conn = new SqlConnection(sqlcon))
                {
                    SqlCommand comm = new SqlCommand(sqlstr, conn);
                    try
                    {
                        conn.Open();
                        SqlDataReader dr = comm.ExecuteReader();
                        dt.Load(dr);
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteSysLog(ex.Message, ex);
                    }
                }
            }
            return dt;
        }
        public int SqlExecuteNon(string sqlstr)
        {

            int count = -1;
            using (SqlConnection conn = new SqlConnection(sqlcon))
            {
                SqlCommand comm = new SqlCommand(sqlstr, conn);
                try
                {
                    conn.Open();
                    count = comm.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Logger.WriteSysLog(ex.Message, ex);
                }
            }
            return count;
        }

        public int GetCount(string sqlstr)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(sqlcon))
            {
                SqlCommand comm = new SqlCommand(sqlstr, conn);
                try
                {
                    conn.Open();
                    int.TryParse(comm.ExecuteScalar().ToString(), out count);
                }
                catch (Exception ex)
                {
                    Logger.WriteSysLog(ex.Message, ex);
                }
            }
            return count;
        }

        public string GetSelectStr<T>(string condition) where T : class
        {
            Type ty = typeof(T);
            string tbName = ty.Name;
            var pros = ty.GetProperties();
            if (pros == null)
            {
                return null;
            }
            List<string> lst = new List<string>();
            foreach (var item in pros)
            {
                lst.Add(item.Name);
            }
            string columnNames = string.Join(",", lst);


            string sqlStr = $"select {columnNames} from {tbName} where {condition} ";
            return sqlStr;
        }

        public string GetDeleteStr<T>(string condition)
        {
            Type ty = typeof(T);
            string tbName = ty.Name;
            string sqlStr = $"delete {tbName} where  {condition} ";
            return sqlStr;
        }

        public string GetInsertStr<T>(T model) where T : class
        {
            Type ty = typeof(T);
            string tbName = ty.Name;

            var pros = ty.GetProperties();
            if (pros == null)
            {
                return null;
            }

            List<string> columns = new List<string>();
            List<string> Values = new List<string>();
            foreach (var item in pros)
            {
                if (item.PropertyType.IsSerializable)
                {
                    columns.Add(item.Name);
                    var obj = item.GetValue(model);
                    if (obj != null)
                    {
                        Values.Add(obj.ToString());
                    }
                }
            }
            string column = string.Join(",", columns);
            string value = "'" + string.Join("','", Values) + "'";
            string sqlstr = $"insert into {tbName}({column}) values( {value})";
            return sqlstr;
        }

        public string GetUpdateStr<T>(T modelold, T modelnew)
        {
            Type t = typeof(T);
            var pros = t.GetProperties();
            Dictionary<string, string> dics = new Dictionary<string, string>();
            Dictionary<string, string> oldVal = new Dictionary<string, string>();
            foreach (var item in pros)
            {
                if (item.PropertyType.IsSerializable)
                {
                    var valold = item.GetValue(modelold);
                    var valnew = item.GetValue(modelnew);
                    if (valnew != valold)
                    {
                        dics.Add(item.Name, valnew.ToString());
                    }
                    else
                    {
                        oldVal.Add(item.Name, valold.ToString());
                    }
                }
            }
            if (dics.Count < 1)
            {
                return null;
            }
            List<string> vals = new List<string>();
            foreach (var item in dics)
            {
                vals.Add($"{item.Key} = '{item.Value}'");
            }
            string strrlt = string.Join(",", vals);
            List<string> conditions = new List<string>();
            foreach (var item in oldVal)
            {
                conditions.Add($"{item.Key} = '{item.Value}'");
            }
            string con = string.Join(",", conditions);
            var sqlstr = $"update {t.Name} set {strrlt} where {con}";
            return sqlstr;
        }
    }
}
